﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using System.Configuration;
using System.Data.Common;

namespace Microsoft.InformationSecurity.CISF.Portal.DataAccess
{
    public class DatabaseHelper
    {
        //private properties.
        public const string ConnectionStringName = "PortalConnectionString";
        private string _ConnectionString;
        private SqlDatabase _db;
        private string _LastErrorDescription;

        /// <summary>
        /// Constructor to create the DatabaseHelper Class.
        /// 
        /// Default SqlServer and Database values read from config file are used to buid the ocnection string.
        /// </summary>
        public DatabaseHelper() 
        {
        }
        private SqlDatabase GetDatabase()
        {
            try
            {
                if (_db != null) return _db;

                //Get the default Connect string paramas from app settings.
                _ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
                if (_ConnectionString == null) throw new Exception("Error in reading the connection string.") ;

                _db = new SqlDatabase(_ConnectionString);
                return _db;

            }
            catch (Exception E)
            {
                _LastErrorDescription = "Error in building the database object.";
                throw E;
            }
        }

        public Boolean IsUserRegistered(string alias)
        {
            try
            {
                Database db = GetDatabase();

                bool IsUserRegistered = false;
                string sqlCommand = "IsUserRegistered";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "IsRegistered", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                IsUserRegistered = (Boolean)db.GetParameterValue(dbCommand, "@IsRegistered");

                return IsUserRegistered;
            }
            catch (Exception E)
            {
                _LastErrorDescription = "IsUserRegistered:: Error in executing IsUserRegistered.";
                throw E;
            }
        }

        public Int32 GetUserId(string alias)
        {
            try
            {
                Database db = GetDatabase();

                Int32 UserId = 0;
                string sqlCommand = "GetUserId";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "UserId", DbType.Int32, 4);

                db.ExecuteNonQuery(dbCommand);

                UserId = (Int32)db.GetParameterValue(dbCommand, "@UserId");

                return UserId;
            }
            catch (Exception E)
            {
                _LastErrorDescription = "GetUserId:: Error in executing GetUserId.";
                throw E;
            }
        }

        public bool CreateDefaultWidgets(string alias)
        {
            try
            {
                Database db = GetDatabase();

                Int16 retVal = 0;
                //Get list of default widgets...
                string sqlCommand = "CreateDefaultWidgets";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "retVal", DbType.Int16, 2);

                db.ExecuteNonQuery(dbCommand);

                retVal = (Int16)db.GetParameterValue(dbCommand, "@retVal");

                if (retVal == 1)
                    return true;

                return false;
            }
            catch (Exception E)
            {
                _LastErrorDescription = "CreateDefaultWidgets:: Error in executing CreateDefaultWidgets.";
                throw E;
            }
        }

        public List<Dictionary<string, string>> GetUserRealWeblets(string alias)
        {
            try
            {
                //Create/Get the database object.
                Database db = GetDatabase();

                string sqlCommand = "GetWidgetInstances";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "@UserAlias", DbType.String, alias);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "@retVal", DbType.Int16, 2);

                //StringBuilder readerData = new StringBuilder();
                List<Dictionary<string, string>> widgetInstances = new List<Dictionary<string, string>>();

                // The ExecuteReader call will request the connection to be closed upon
                // the closing of the DataReader. The DataReader will be closed 
                // automatically when it is disposed.
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    // Iterate through DataReader and put results to the text box.
                    // DataReaders cannot be bound to Windows Form controls (e.g. the
                    // resultsDataGrid), but may be bound to Web Form controls.
                    while (dataReader.Read())
                    {
                        Dictionary<string, string> widgetInstance = new Dictionary<string, string>();
                        //if (readerData.Length > 0)
                        //    readerData.Append(";");

                        // Find the total nuumber of columns
                        int count = dataReader.FieldCount;
                        for (int i = 0; i < count; i++)
                        {
                            string key = string.Empty, val = string.Empty;

                            //if ( i > 0 )
                            //    readerData.Append(",");
                            //readerData.Append(dataReader.GetName(i).ToString());
                            //readerData.Append("=");
                            //readerData.Append(dataReader.GetValue(i).ToString());

                            //construct widgetInstance.
                            key = dataReader.GetName(i).ToString();
                            val = dataReader.GetValue(i).ToString();
                            widgetInstance.Add(key, val);
                        }
                        widgetInstances.Add(widgetInstance);
                    }
                }
                return widgetInstances;
                //return readerData.ToString();
            }
            catch (Exception E)
            {
                _LastErrorDescription = "GetUserRealWeblets:: Error in executing GetUserRealWeblets.";
                throw E;
            }
        }
        public List<Dictionary<string, string>> GetWidgetList()
        {
            try
            {
                //Create/Get the database object.
                Database db = GetDatabase();

                string sqlCommand = "GetWidgets";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "@retVal", DbType.Int16, 2);

                List<Dictionary<string, string>> widgets = new List<Dictionary<string, string>>();

                // The ExecuteReader call will request the connection to be closed upon
                // the closing of the DataReader. The DataReader will be closed 
                // automatically when it is disposed.
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    // Iterate through DataReader and put results to the text box.
                    // DataReaders cannot be bound to Windows Form controls (e.g. the
                    // resultsDataGrid), but may be bound to Web Form controls.
                    while (dataReader.Read())
                    {
                        Dictionary<string, string> widget = new Dictionary<string, string>();

                        // Find the total nuumber of columns
                        int count = dataReader.FieldCount;
                        for (int i = 0; i < count; i++)
                        {
                            string key = string.Empty, val = string.Empty;


                            //construct widgetInstance.
                            key = dataReader.GetName(i).ToString();
                            val = dataReader.GetValue(i).ToString();
                            widget.Add(key, val);
                        }
                        widgets.Add(widget);
                    }
                }
                return widgets;
            }
            catch (Exception E)
            {
                _LastErrorDescription = "GetWidgetList:: Error in executing GetWidgetList.";
                throw E;
            }
        }
        public bool ModifyWidgetSize(string alias, Int32 WidgetId, int val)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "UpdateWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                db.AddInParameter(dbCommand, "Expanded", DbType.Int16, val);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "ModifyWidgetSize:: Error in executing ModifyWidgetSize.";
                throw E;
            }
        }
        public bool SaveWidgetState(string alias, Int32 WidgetId, string State)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "UpdateWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                db.AddInParameter(dbCommand, "State", DbType.String, State);

                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "SaveWidgetState:: Error in executing SaveWidgetState.";
                throw E;
            }
        }
        public bool SaveWidgetPosition(string alias, Int32 WidgetId, int Row, int Col)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "UpdateWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                db.AddInParameter(dbCommand, "ColumnNo", DbType.Int16, 2);
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int16, 2);

                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "SaveWidgetPosition:: Error in executing SaveWidgetPosition.";
                throw E;
            }
        }
        public bool AddWidget(string alias, Int32 WidgetId, int Row, int Col, int Expanded, string Title, string State, int VersionNo)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "AddWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                db.AddInParameter(dbCommand, "ColumnNo", DbType.Int16, Col);
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int16, Row);
                db.AddInParameter(dbCommand, "Expanded", DbType.Int16, Expanded);
                db.AddInParameter(dbCommand, "Title", DbType.String, Title);
                db.AddInParameter(dbCommand, "State", DbType.String, State);
                db.AddInParameter(dbCommand, "VersionNo", DbType.Int16, VersionNo);

                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "AddWidget:: Error in executing AddWidget.";
                throw E;
            }
        }

        public bool UpdateWidget(string alias, Int32 WidgetId, int Row, int Col, int Expanded, string Title, string State, int VersionNo)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "UpdateWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                db.AddInParameter(dbCommand, "ColumnNo", DbType.Int16, Col);
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int16, Row);
                db.AddInParameter(dbCommand, "Expanded", DbType.Int16, Expanded);
                db.AddInParameter(dbCommand, "Title", DbType.String, Title);
                db.AddInParameter(dbCommand, "State", DbType.String, State);
                db.AddInParameter(dbCommand, "VersionNo", DbType.Int16, VersionNo);

                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "UpdateWidget:: Error in executing UpdateWidget.";
                throw E;
            }
        }
        public bool DeleteWidget(string alias, Int32 WidgetId)
        {
            try
            {
                Database db = GetDatabase();

                string sqlCommand = "DeleteWidget";
                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                // Add paramters
                // Input parameters can specify the input value
                db.AddInParameter(dbCommand, "UserAlias", DbType.String, alias);
                db.AddInParameter(dbCommand, "WidgetId", DbType.Int32, WidgetId);
                // Output parameters specify the size of the return data
                db.AddOutParameter(dbCommand, "RetVal", DbType.Boolean, 1);

                db.ExecuteNonQuery(dbCommand);

                bool res = (bool)db.GetParameterValue(dbCommand, "@RetVal");

                return res; //0 = Success
            }
            catch (Exception E)
            {
                _LastErrorDescription = "DeleteWidget:: Error in executing DeleteWidget.";
                throw E;
            }
        }


    }
}
